package giee.com.sqltransfer.ToExcel;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import giee.com.sqltransfer.utils.TableNameRules;
import javax.swing.*;
import java.awt.*;
import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


public class preImport extends JFrame {
    Map<String,String> excelNameRules = TableNameRules.getTableType();
    String targetExcelNameRule = excelNameRules.keySet().toArray(new String[0])[0];
    String sqlFileName;
    List<String> excelHead = new ArrayList<>();
    List<List<String>> excelData = new ArrayList<>();

    preImport(List<SQLStatement> statements, String sqlFileName) {
        this.sqlFileName = sqlFileName;
        for(SQLStatement statement : statements) {

            if(statement.type.equals("CREATE TABLE")) {
                parseTableFieldsToExcelHead(statement.original);
            }
            else if(statement.type.equals("INSERT")) {
                parseInsertDataToExcelData(statement.original);
            }
        }

//        for(int i = 0; i < excelData.size(); i++) {
//            for(String field : excelData.get(i)) {
//                System.out.printf(field + "\t");
//            }
//            System.out.println();
//        }

        initUi();       // 初始化ui界面
    }

    /**
     * 初始化界面，展示解析sql文件的结果
     */
    public void initUi() {
        setTitle("SQL数据预览 - 即将导入Excel");
        setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
        setSize(800, 600);
        setLocationRelativeTo(null); // 居中显示

        // 主面板使用BorderLayout
        JPanel mainPanel = new JPanel(new BorderLayout(10, 10));
        mainPanel.setBorder(BorderFactory.createEmptyBorder(10, 10, 10, 10));

        // 顶部信息面板
        JPanel topPanel = new JPanel(new BorderLayout());
        JLabel titleLabel = new JLabel("SQL数据预览", JLabel.CENTER);
        titleLabel.setFont(new Font("微软雅黑", Font.BOLD, 18));
        topPanel.add(titleLabel, BorderLayout.NORTH);

        // 数据统计信息
        JPanel infoPanel = new JPanel(new FlowLayout(FlowLayout.LEFT));
        infoPanel.add(new JLabel("字段数: " + excelHead.size()));
        infoPanel.add(new JLabel("数据行数: " + excelData.size()));
        topPanel.add(infoPanel, BorderLayout.SOUTH);

        mainPanel.add(topPanel, BorderLayout.NORTH);

        // 中间表格面板
        String[] columnNames = excelHead.toArray(new String[0]);
        String[][] rowData = new String[excelData.size()][];

        for (int i = 0; i < excelData.size(); i++) {
            rowData[i] = excelData.get(i).toArray(new String[0]);
        }

        JTable table = new JTable(rowData, columnNames);
        table.setFont(new Font("微软雅黑", Font.PLAIN, 14));
        table.setRowHeight(25);
        table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);

        // 设置列宽自适应内容
        for (int i = 0; i < table.getColumnModel().getColumnCount(); i++) {
            int width = 100; // 默认宽度
            // 根据列名长度调整
            width = Math.max(width, columnNames[i].length() * 10);
            // 根据内容长度调整
            for (int j = 0; j < rowData.length; j++) {
                if (rowData[j][i] != null) {
                    width = Math.max(width, rowData[j][i].length() * 8);
                }
            }
            table.getColumnModel().getColumn(i).setPreferredWidth(width);
        }

        JScrollPane scrollPane = new JScrollPane(table);
        scrollPane.setBorder(BorderFactory.createTitledBorder("数据预览"));
        mainPanel.add(scrollPane, BorderLayout.CENTER);

        // 底部按钮面板
        JPanel bottomPanel = new JPanel(new BorderLayout(10, 10));
        bottomPanel.setBorder(BorderFactory.createEmptyBorder(10, 10, 10, 10));

        // 创建左侧设置面板（标签+下拉框）
        JPanel setupPanel = new JPanel(new FlowLayout(FlowLayout.LEFT, 10, 0));
        JLabel label = new JLabel("导入表类型:");
        JComboBox<String> box = new JComboBox<>(excelNameRules.keySet().toArray(new String[0]));
        box.setPreferredSize(new Dimension(150, 25)); // 设置合适的下拉框宽度
        setupPanel.add(label);
        setupPanel.add(box);
        
        // 下拉菜单默认选中第1个菜单项
        box.setSelectedItem(0);

        // 添加下拉菜单事件监听
        box.addActionListener(e -> {
            if (e.getSource() == box) {
                String selectedType = (String) box.getSelectedItem();
                targetExcelNameRule = excelNameRules.get(selectedType);
            }
        });
        // 创建右侧按钮面板
        JPanel buttonPanel = new JPanel(new FlowLayout(FlowLayout.RIGHT, 10, 0));
        JButton confirmButton = new JButton("确认导入Excel");
        JButton cancelButton = new JButton("取消");

        confirmButton.addActionListener(e -> {
            exportToExcel();
            dispose();
        });

        cancelButton.addActionListener(e -> dispose());

        buttonPanel.add(confirmButton);
        buttonPanel.add(cancelButton);

// 将设置面板和按钮面板添加到底部面板
        bottomPanel.add(setupPanel, BorderLayout.WEST);
        bottomPanel.add(buttonPanel, BorderLayout.EAST);

        mainPanel.add(bottomPanel, BorderLayout.SOUTH);
        add(mainPanel);
        setVisible(true);
    }

    /**
     * 解析CREATE TABLE SQL语句，准确提取所有字段名到excelHead列表中
     * @param createTableSql CREATE TABLE语句字符串
     */
    public void parseTableFieldsToExcelHead(String createTableSql) {
        excelHead.clear();

        try {
            // 提取表定义部分（去除表选项）
            int start = createTableSql.indexOf("(");
            int end = createTableSql.lastIndexOf(")");

            if (start > 0 && end > start) {
                String tableDefinition = createTableSql.substring(start + 1, end);

                // 分割每一行定义
                String[] lines = tableDefinition.split(",\\s*");

                for (String line : lines) {
                    line = line.trim();
                    // 跳过主键、索引等非字段定义行
                    if (line.startsWith("PRIMARY KEY") ||
                            line.startsWith("UNIQUE KEY") ||
                            line.startsWith("KEY") ||
                            line.startsWith("CONSTRAINT")) {
                        continue;
                    }

                    // 提取字段名（支持`、'、"三种引号或无引号）
                    if (line.matches("^[`'\"]?\\w+[`'\"]?\\s+\\w+.*")) {
                        String fieldName = line.replaceAll("^[`'\"]?(\\w+)[`'\"]?.*", "$1");
                        excelHead.add(fieldName);
                    }
                }
            } else {
                throw new IllegalArgumentException("不是有效的CREATE TABLE语句");
            }

        } catch (Exception e) {
            System.err.println("解析SQL字段失败: " + e.getMessage());
            excelHead.clear();
        }
    }

    /**
     * 解析INSERT SQL语句，将数据存入excelData列表中
     * @param insertSql INSERT语句字符串
     */
    public void parseInsertDataToExcelData(String insertSql) {

        try {
            // 更可靠的方法提取VALUES部分
            int valuesIndex = insertSql.toUpperCase().indexOf("VALUES");
            if (valuesIndex < 0) {
                throw new IllegalArgumentException("不是有效的INSERT语句");
            }

            String valuesPart = insertSql.substring(valuesIndex + "VALUES".length()).trim();

            // 提取括号内的内容
            if (valuesPart.startsWith("(") && valuesPart.endsWith(")")) {
                valuesPart = valuesPart.substring(1, valuesPart.length() - 1);
            } else if (valuesPart.startsWith("(")) {
                // 处理多行VALUES情况
                valuesPart = valuesPart.substring(1);
            }

            // 分割各个值（考虑引号包裹的字符串可能包含逗号）
            List<String> rowData = new ArrayList<>();
            boolean inQuote = false;
            StringBuilder currentValue = new StringBuilder();

            for (int i = 0; i < valuesPart.length(); i++) {
                char c = valuesPart.charAt(i);

                if (c == '\'') {
                    inQuote = !inQuote;
                    currentValue.append(c);
                } else if (c == ',' && !inQuote) {
                    // 遇到非引号内的逗号，分割值
                    rowData.add(currentValue.toString().trim());
                    currentValue.setLength(0);
                } else {
                    currentValue.append(c);
                }
            }

            // 添加最后一个值
            if (currentValue.length() > 0) {
                rowData.add(currentValue.toString().trim());
            }

            // 去除值两端的单引号
            for (int i = 0; i < rowData.size(); i++) {
                String val = rowData.get(i);
                if (val.startsWith("'") && val.endsWith("'")) {
                    rowData.set(i, val.substring(1, val.length() - 1));
                }
            }

            excelData.add(rowData);

        } catch (Exception e) {
            System.err.println("解析INSERT语句失败: " + e.getMessage());
            excelData.clear();
        }
    }

    /**
     * 导出数据到Excel文件
     */
    public void exportToExcel() {
        // 检查是否有数据可导出
        if (excelHead.isEmpty() || excelData.isEmpty()) {
            JOptionPane.showMessageDialog(this,
                    "没有数据可导出", "错误", JOptionPane.ERROR_MESSAGE);
            return;
        }

        // 创建文件选择器
        JFileChooser fileChooser = new JFileChooser();
        fileChooser.setDialogTitle("选择保存位置");
        fileChooser.setSelectedFile(new File(sqlFileName + "_" + targetExcelNameRule + ".xlsx"));

        // 显示保存对话框
        int userSelection = fileChooser.showSaveDialog(this);
        if (userSelection != JFileChooser.APPROVE_OPTION) {
            return; // 用户取消
        }

        File outputFile = fileChooser.getSelectedFile();
        // 确保文件扩展名是.xlsx
        if (!outputFile.getName().toLowerCase().endsWith(".xlsx")) {
            outputFile = new File(outputFile.getAbsolutePath() + ".xlsx");
        }

        try {
            // 使用EasyExcel写入数据
            EasyExcel.write(outputFile)
                    .head(generateHead()) // 设置表头
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 自动列宽
                    .sheet("Sheet1") // 设置sheet名称
                    .doWrite(generateData()); // 写入数据

            JOptionPane.showMessageDialog(this,
                    "数据已成功导出到: " + outputFile.getAbsolutePath(),
                    "导出成功", JOptionPane.INFORMATION_MESSAGE);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(this,
                    "导出失败: " + e.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
        }
    }

    /**
     * 生成EasyExcel需要的表头格式（兼容Java 8）
     */
    private List<List<String>> generateHead() {
        List<List<String>> head = new ArrayList<>();
        for (String field : excelHead) {
            List<String> headItem = new ArrayList<>();
            headItem.add(field);
            head.add(headItem);
        }
        return head;
    }

    /**
     * 生成EasyExcel需要的数据格式（兼容Java 8）
     */
    private List<List<Object>> generateData() {
        List<List<Object>> data = new ArrayList<>();
        for (List<String> row : excelData) {
            List<Object> dataRow = new ArrayList<>(row);
            data.add(dataRow);
        }
        return data;
    }
}
